#! /usr/bin/python

"""
sets all "name" tags of "node","way" to the specified language
	    ######################
	    # now is the time to change the attributes before they are written to the file

	    # modify "name" according to the rule:
	    #   name:he,name:de,name      # if the others don't exist, take "name"
	    #   name:he,name,name:de,name # "name" may be in the middle of the list
	    #   name:he,name:de           # if "name" does not appear in the list, in will be deleted if it exists
	    srcTags = ["name:en","name","name:he",]
	    dstTag = "name"
"""

import sys
import getopt
import xml.sax
from xml.sax.saxutils import escape, quoteattr
from pysqlite2 import dbapi2 as sqlite

num_points = 0

class OSMXMLFileParser(xml.sax.ContentHandler):
    def __init__(self, glb_vars):
        #self.glb_vars = glb_vars
	self.nest = ""
	self.curr_tags = None
	self.curr_attrs = None
	self.node_pos = 0 # node position in the line (0..n-1) for line with n nodes
	self.tag_container = None
 
    def startElement(self, name, attrs):
	#self.nest += " " # increment the nesting level

	if name == 'node' or name == 'way' or name == 'relation':
            assert self.curr_tags == None
	    self.curr_tags = {}
	    self.curr_attrs = attrs
	    self.node_pos = 0
	    self.tag_container = name
	    global num_points
	    num_points += 1
	    if num_points % 100000 == 0:
		    print num_points, "nodes"
	if name == 'node':
	    cursor.execute('INSERT INTO nodes VALUES (?,?,?)',(int(attrs["id"]),float(attrs["lon"]),float(attrs["lat"])))
	if name == 'nd':
	    cursor.execute('INSERT INTO line_nodes VALUES (?,?,?)',(int(self.curr_attrs["id"]),int(attrs["ref"]),self.node_pos))
	    self.node_pos += 1
	
        elif name == 'tag':
            assert self.curr_tags != None
            self.curr_tags[attrs['k']] = attrs['v']
	    # "OR REPLACE is a workaround"
	    # when processin austria.osm it gives:
	    #    pysqlite2.dbapi2.IntegrityError: columns obj_type, obj_id, k are not unique
	    cursor.execute('INSERT OR REPLACE INTO tags VALUES (?,?,?,?)',(self.tag_container, # obj_type
	                                                        int(self.curr_attrs["id"]), # obj_id
								attrs['k'],                 # k
								attrs['v'])                 # v
			  )
	return
 
    def endElement(self, name):
	if name == 'node' or name == 'way' or name == 'relation':
            assert self.curr_tags != None
	    self.curr_tags = None

	#self.nest = self.nest[1:] # decrement the nesting level


######################## main #########################
DB_FILE="/tmp/osm.db"
def sqlite_import_osm():
	# global variables
	glb_vars = {}
	
	if len(sys.argv)>0:
		infile = sys.argv[1]
		print >>sys.stderr, "reading from:", infile
	else:
		infile = sys.stdin
		print >>sys.stderr, "reading from: STDIN"
	
	connection = sqlite.connect(DB_FILE)
	global cursor
	cursor = connection.cursor()
	
	# delete any previous table
	cursor.execute('DROP TABLE IF EXISTS nodes')
	cursor.execute('CREATE TABLE IF NOT EXISTS nodes (id INTEGER PRIMARY KEY, lon REAL, lat REAL)')
	
	cursor.execute('DROP TABLE IF EXISTS line_nodes')
	cursor.execute('CREATE TABLE IF NOT EXISTS line_nodes (line_id INTEGER, node_id INTEGER, pos INTEGER, PRIMARY KEY(line_id,node_id,pos))')
	
	cursor.execute('DROP TABLE IF EXISTS tags')
	cursor.execute('CREATE TABLE IF NOT EXISTS tags (obj_type TEXT, obj_id INTEGER, k TEXT, v TEXT, PRIMARY KEY(obj_type,obj_id,k))')
	
	parser = xml.sax.make_parser()
	parser.setContentHandler(OSMXMLFileParser(glb_vars))
	parser.parse(infile)
	
	connection.commit() # save, (commit) the changes

#------------------------------------------------------------------------------------
ROADMAP_ROAD_FIRST      = 1
ROADMAP_ROAD_FREEWAY    = 1
ROADMAP_ROAD_PRIMARY    = 2
ROADMAP_ROAD_SECONDARY  = 3
ROADMAP_ROAD_RAMP       = 4
ROADMAP_ROAD_MAIN       = 5
ROADMAP_ROAD_EXIT       = 6
ROADMAP_ROAD_STREET     = 7
ROADMAP_ROAD_PEDESTRIAN = 8
ROADMAP_ROAD_4X4        = 9
ROADMAP_ROAD_TRAIL      =10 
ROADMAP_ROAD_WALKWAY    =11
ROADMAP_ROAD_LAST       =11
ROADMAP_AREA_FIRST      =12
ROADMAP_AREA_PARC       =12
ROADMAP_AREA_HOSPITAL   =13
ROADMAP_AREA_AIRPORT    =14
ROADMAP_AREA_STATION    =15
ROADMAP_AREA_MALL       =16
ROADMAP_AREA_LAST       =16
ROADMAP_WATER_FIRST     =17
ROADMAP_WATER_SHORELINE =17
ROADMAP_WATER_RIVER     =18
ROADMAP_WATER_LAKE      =19
ROADMAP_WATER_SEA       =20
ROADMAP_WATER_LAST      =20
ROADMAP_CATEGORY_RANGE  =20
def get_way_cfcc_type(highway,railway,waterway,natural):
    if highway != None:
	    if highway == "motorway":
		    return ROADMAP_ROAD_FREEWAY
	    elif highway == "motorway_link":
		    return ROADMAP_ROAD_RAMP
	    elif highway == "trunk":
		    return ROADMAP_ROAD_FREEWAY
	    elif highway == "trunk_link":
		    return ROADMAP_ROAD_RAMP
	    elif highway == "primary":
		    return ROADMAP_ROAD_PRIMARY
	    elif highway == "primary_link":
		    return ROADMAP_ROAD_RAMP
	    elif highway == "secondary":
		    return ROADMAP_ROAD_SECONDARY
	    elif highway == "tertiary":
		    return ROADMAP_ROAD_MAIN
	    elif highway == "unclassified":
		    return ROADMAP_ROAD_STREET
	    elif highway == "residential":
		    return ROADMAP_ROAD_STREET
	    elif highway == "living_street":
		    return ROADMAP_ROAD_PEDESTRIAN
	    elif highway == "pedestrian":
		    return ROADMAP_ROAD_PEDESTRIAN
	    elif highway == "steps":
		    return ROADMAP_ROAD_WALKWAY
	    elif highway == "footway" or highway == "footpath":
		    return ROADMAP_ROAD_WALKWAY
	    else:
		    return ROADMAP_ROAD_STREET
    elif railway != None:
	    return ROADMAP_ROAD_TRAIL  # TODO this is a patch. no proper cfcc type for this??
    elif waterway != None:
	    return ROADMAP_WATER_FIRST # TODO use more specific types
    elif natural!=None and natural=="water":
	    return ROADMAP_WATER_FIRST # TODO use more specific types
    else:
	    # TODO: support area and water types
	    return ROADMAP_ROAD_STREET

def get_way_db_type(highway,railway,waterway,natural):
	cfcc = get_way_cfcc_type(highway,railway,waterway,natural)
	if cfcc == ROADMAP_ROAD_STREET:
		return 1
	if cfcc == ROADMAP_ROAD_MAIN:
		return 2
	if cfcc == ROADMAP_ROAD_FREEWAY:
		return 3
	if cfcc == ROADMAP_ROAD_PRIMARY:
		return 6
	if cfcc == ROADMAP_ROAD_SECONDARY:
		return 7
	if cfcc == ROADMAP_ROAD_RAMP:
		return 4
	if cfcc == ROADMAP_ROAD_TRAIL:
		return 5
	if cfcc == ROADMAP_ROAD_4X4:
		return 8
	if cfcc == ROADMAP_ROAD_WALKWAY:
		return 9
	if cfcc == ROADMAP_ROAD_PEDESTRIAN:
		return 10
	if cfcc == ROADMAP_ROAD_EXIT:
		return 11
	if cfcc == ROADMAP_ROAD_STREET:
		return 12
	return 12

# help sqlite db functions
def _strcat(x,y):
	return str(x)+str(y)
def _linestring(lon1,lat1,lon2,lat2):
	return "LINESTRING(%s %s,%s %s)" % ( lon1, lat1, lon2, lat2 )
	#return "LINESTRING(%.4f %.4f,%.4f %.4f)" % ( float(lon1), float(lat1), float(lon2), float(lat2) )
def sqlite_to_buildmap():
	connection = sqlite.connect(DB_FILE)
	cursor = connection.cursor()
	
	connection.create_function("strcat", 2, _strcat)
	connection.create_function("linestring", 4, _linestring)
	connection.create_function("get_way_db_type", 4, get_way_db_type)
	
	cursor.execute('CREATE TABLE IF NOT EXISTS cities (name TEXT)')
	cursor.execute('CREATE TABLE IF NOT EXISTS country_borders_sql (tlid TEXT, the_geom TEXT)')
	
	#cursor.execute('DROP TABLE IF EXISTS roads_sql')
	#cursor.execute("""CREATE TABLE IF NOT EXISTS roads_sql (
								#id TEXT,
								#the_geom TEXT,
								#layer TEXT,
								#from_node_id TEXT,
								#to_node_id TEXT,
								#street_type TEXT,
								#street_name TEXT,
								#text2speech TEXT,
								#city_name TEXT,
								#fraddl TEXT,
								#toaddl TEXT,
								#fraddr TEXT,
								#toaddr TEXT,
								#from_travel_ref TEXT,
								#to_travel_ref TEXT)""")
	
	cursor.execute('DROP TABLE IF EXISTS roads_sql')
	cursor.execute("""
CREATE TABLE roads_sql AS
		SELECT
			"" as id,
			linestring(frNd.lon,frNd.lat,toNd.lon,toNd.lat) as the_geom,
			get_way_db_type(highway_tag.v,null,null,null) as layer,	/* get_way_cfcc_type(highway,railway,waterway,natural) */
			"" as from_node_id,
			"" as to_node_id,
			"" as street_type,
			ifnull(name_tag.v,"") as street_name,
			"" as text2speech,
		/* temp - use street also name as city name for roadmap */
			ifnull(name_tag.v,"") as city_name,
			"" as fraddl,
			"" as toaddl,
			"" as fraddr,
			"" as toaddr,
			"" as from_travel_ref,
			"" as to_travel_ref
		FROM
			line_nodes as frLn
			INNER JOIN line_nodes as toLn
				ON frLn.line_id = toLn.line_id and frLn.pos = toLn.pos-1
			INNER JOIN nodes as frNd
				ON frLn.node_id = frNd.id
			INNER JOIN nodes as toNd
				ON toLn.node_id = toNd.id
			LEFT OUTER JOIN tags as name_tag
				ON frLn.line_id=name_tag.obj_id and name_tag.obj_type="way" and name_tag.k="name"
			LEFT OUTER JOIN tags as highway_tag
				ON frLn.line_id=highway_tag.obj_id and highway_tag.obj_type="way" and highway_tag.k="highway"
		""")
	for row in cursor:
		print row

	connection.commit() # save, (commit) the changes

#sqlite_import_osm()
sqlite_to_buildmap()